package com.itheima;

import com.itheima.mapper.UserMapper;
import com.itheima.pojo.User;
import com.itheima.service.UserService;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.io.FileOutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

/**
 * @author 张一恒
 * @version 1.0
 * @description:
 * @date 2022/12/11 16:57
 */
@SpringBootTest
public class POIDemo04 {

    @Autowired
    UserService userService;

    @Autowired
    private UserMapper userMapper;

    private static SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-ss");

    public List<User> findAll() {
        return userMapper.selectAll();
    }

    @Test
    public void testPoi() throws Exception {
        //设置标题
        //创建工作簿
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("一个带样式的工作表");

        //        设置列宽
        sheet.setColumnWidth(0, 5 * 256);
        sheet.setColumnWidth(1, 8 * 256);
        sheet.setColumnWidth(2, 15 * 256);
        sheet.setColumnWidth(3, 15 * 256);
        sheet.setColumnWidth(4, 30 * 256);

        //带边框线、行高、合并单元格、对齐方式、字体

        CellStyle bigTitleRowCellStyle = workbook.createCellStyle(); //大标题列样式设置
        //设置边框线
        bigTitleRowCellStyle.setBorderBottom(BorderStyle.THIN); //下
        bigTitleRowCellStyle.setBorderLeft(BorderStyle.THIN); //左
        bigTitleRowCellStyle.setBorderRight(BorderStyle.THIN); //右
        bigTitleRowCellStyle.setBorderTop(BorderStyle.THIN); //上

        Row bigRow = sheet.createRow(0); //第一行
        bigRow.setHeightInPoints(42);   //设置行高
        for (int i = 0; i < 5; i++) {
            Cell cell = bigRow.createCell(i); //列
            cell.setCellStyle(bigTitleRowCellStyle); //设置列样式
        }

        //合并单元格
        //int firstRow 第一行, int lastRow 最后一行, int firstCol 第一列, int lastCol 最后一列
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 4));

        //对齐方式
        //居中对齐：
        bigTitleRowCellStyle.setAlignment(HorizontalAlignment.CENTER); //水平居中
        bigTitleRowCellStyle.setVerticalAlignment(VerticalAlignment.CENTER); //垂直居中

        sheet.getRow(0).getCell(0).setCellValue("用户信息数据"); //输出字符

        //设置字体
        Font font = workbook.createFont();
        font.setFontName("黑体"); //字体格式
        font.setFontHeightInPoints((short) 18); //字体大小
        bigTitleRowCellStyle.setFont(font); //设置样式


        //小标题列样式设置
        CellStyle smallTitleRowCellStyle = workbook.createCellStyle();
        smallTitleRowCellStyle.cloneStyleFrom(bigTitleRowCellStyle);

        //设置字体
        Font smallFont = workbook.createFont();
        smallFont.setFontName("宋体"); //字体格式
        smallFont.setFontHeightInPoints((short) 12); //字体大小
        smallFont.setBold(true); //加粗
        smallTitleRowCellStyle.setFont(smallFont); //设置样式

        String[] smallTitle = new String[]{"编号", "姓名", "手机号", "入职日期", "现住址"};

        Row smallRow = sheet.createRow(1);
        smallRow.setHeightInPoints(32);   //设置行高
        for (int i = 0; i < smallTitle.length; i++) {
            Cell cell = smallRow.createCell(i);
            cell.setCellValue(smallTitle[i]);
            cell.setCellStyle(smallTitleRowCellStyle);
        }


        //单元格内容列样式设置
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.cloneStyleFrom(bigTitleRowCellStyle);

        //设置字体
        Font cellStyleFont = workbook.createFont();
        cellStyleFont.setFontName("宋体"); //字体格式
        cellStyleFont.setFontHeightInPoints((short) 11); //字体大小
        cellStyleFont.setBold(false); //加粗
        cellStyle.setFont(cellStyleFont); //设置样式
        cellStyle.setAlignment(HorizontalAlignment.LEFT); //水平居中

      /*  List<User> userList = userService.findAll();

        int cellStyleRowCount = 2;
        Row cellStyleRow = null;
        Cell cell = null;
        for (User user : userList) {
            cellStyleRow = sheet.createRow(cellStyleRowCount);
            cell = cellStyleRow.createCell(0);
            cell.setCellValue(user.getId());
            cell.setCellStyle(cellStyle);

            cell = cellStyleRow.createCell(1);
            cell.setCellValue(user.getUserName());
            cell.setCellStyle(cellStyle);

            cell = cellStyleRow.createCell(2);
            cell.setCellValue(user.getPhone());
            cell.setCellStyle(cellStyle);

            cell = cellStyleRow.createCell(3);
            cell.setCellValue(simpleDateFormat.format(user.getHireDate()));
            cell.setCellStyle(cellStyle);

            cell = cellStyleRow.createCell(4);
            cell.setCellValue(user.getAddress());
            cell.setCellStyle(cellStyle);

            cellStyleRowCount++;
        }*/

        String[] ss = new String[]{"1", "大一", "13800000001", "2001-03-29", "北京市西城区宣武大街1号院"};

        Row cellStyleRow = sheet.createRow(2);
        for (int i = 0; i < ss.length; i++) {
            Cell cell = cellStyleRow.createCell(i);
            cell.setCellValue(ss[i]);
            cell.setCellStyle(cellStyle);
        }


        workbook.write(new FileOutputStream("D:\\practice\\excel-practice\\testStyle.xlsx"));

    }
}
